using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;
namespace DAO
{
    public class DonGiaDAO
    {

        DataProvider Provider;
        public DonGiaDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable LayBangThuoc_DonGia()
        {
            Provider.Connect();
            string str = "dg.MaLoaiThuoc, t.TenLoaiThuoc, dg.MaDonVi, dv.TenDV, dg.DonGia  from DONGIA dg, DONVI dv, THUOC t where t.MaLoaiThuoc = dg.MaLoaiThuoc and dg.MaDonVi = dv.MaDV";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangDonGia()
        {
            Provider.Connect();
            string str = "select dg.MaLoaiThuoc as MaLoaiThuoc3, t.TenLoaiThuoc as TenLoaiThuoc3, dg.MaDonVi as MaDonVi3, dv.TenDV as TenDV3, dg.DonGia as DonGia3 from DONGIA dg, DONVI dv, THUOC t where dg.MaLoaiThuoc = t.MaLoaiThuoc and dg.MaDonVi = dv.MaDV";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangDonGiaTheoMaThuoc(string maLoaiThuoc)
        {
            Provider.Connect();
            string str = "select dg.MaLoaiThuoc, t.TenLoaiThuoc, dg.MaDonVi, dv.TenDV, dg.DonGia  from DONGIA dg, DONVI dv, THUOC t where dg.MaLoaiThuoc = t.MaLoaiThuoc and dg.MaDonVi = dv.MaDV and dg.MaLoaiThuoc = '" + maLoaiThuoc + "'";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public void ThemDonGia(DonGiaDTO dgDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into DONGIA(MaLoaiThuoc, MaDonVi, DonGia) values (?, ?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);
            cmd.Parameters.Add("@MaDonVi", OleDbType.WChar);
            cmd.Parameters.Add("@DonGia", OleDbType.Integer);

            cmd.Parameters["@MaLoaiThuoc"].Value = dgDto.MaLoaiThuoc;
            cmd.Parameters["@MaDonVi"].Value = dgDto.MaDV;
            cmd.Parameters["@DonGia"].Value = dgDto.DonGia;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }


        public void XoaDonGia(string MaLoaiThuoc, string MaDonVi)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From DONGIA dg Where dg.MaLoaiThuoc = ? and dg.MaDonVi = ?";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.Char);
            cmd.Parameters.Add("@MaDonVi", OleDbType.Char);
            cmd.Parameters["@MaLoaiThuoc"].Value = MaLoaiThuoc;
            cmd.Parameters["@MaDonVi"].Value = MaDonVi;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }

        public void SuaDonGia(DonGiaDTO dgDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update DONGIA Set DonGia = ?  Where MaLoaiThuoc = '" + dgDto.MaLoaiThuoc + "' and MaDonVi = '" + dgDto.MaDV + "'";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@DonGia", OleDbType.Integer);
            cmd.Parameters["@DonGia"].Value = dgDto.DonGia;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }


        public DataTable TimKiemTheoMa(string MaLoaiThuoc, string MaDonVi)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From DonGia Where MaLoaiThuoc = " + "'" + MaLoaiThuoc + "'" + " and MaDonVi = " + "'" + MaDonVi + "'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            cmd.Parameters.Add("@MaLoaiThuoc", OleDbType.WChar);
            cmd.Parameters["@MaLoaiThuoc"].Value = MaLoaiThuoc;
            cmd.Parameters.Add("@MaDonVi", OleDbType.WChar);
            cmd.Parameters["@MaDonVi"].Value = MaDonVi;

            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }
    }
}
